CREATE TABLE Users 
(
UserID INT NOT NULL,
Username varchar2(20) NOT NULL UNIQUE,
FName varchar2(20) NOT NULL,
LName varchar2(20) NOT NULL,
ZipCode INT NOT NULL,
Email varchar2(60) NOT NULL,
LoginIP varchar2(20),
PRIMARY KEY (UserID)
);

CREATE SEQUENCE userSeq	
START WITH 1
INCREMENT BY 1
NOMAXVALUE;

CREATE OR REPLACE TRIGGER userTrigger
BEFORE INSERT
ON Users
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT userSeq.nextval INTO :NEW.UserID FROM dual;
END;
/

CREATE TABLE Authenticate
(
UserID INT NOT NULL,
Password varchar2(40) NOT NULL,
Role INT NOT NULL,
PRIMARY KEY (UserID),
FOREIGN KEY (UserID) REFERENCES Users (UserID) ON DELETE CASCADE
);

CREATE TABLE UserLog
(
LogID INT NOT NULL,
UserID INT,
logDate DATE NOT NULL,
Action varchar2(400) NOT NULL,
IPAddress varchar2 (20),
PRIMARY KEY (LogID, UserID),
FOREIGN KEY (UserID) REFERENCES Users (UserID) ON DELETE CASCADE
);

CREATE SEQUENCE logSeq
START WITH 1
INCREMENT BY 1
NOMAXVALUE;

CREATE OR REPLACE TRIGGER logTrigger
BEFORE INSERT
ON UserLog
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT logSeq.nextval INTO :NEW.LogID FROM dual;
END;
/


CREATE TABLE Movies
(
MovId INT NOT NULL,
MovTitle varchar2(40) NOT NULL,
SubmitterID INT,
MovDesc varchar2(2000),
MovYear INT,
MovBudget varchar2(20),
MovRunTime varchar2(20),
DateAdded DATE NOT NULL DEFAULT SYSDATE,
PendStatus INT NOT NULL,
DateAccepted DATE NOT NULL DEFAULT SYSDATE,
ImgCover VARCHAR2(100),
Trailer VARCHAR2(100),
PRIMARY KEY (MovID)
);

CREATE SEQUENCE movSeq
START WITH 1
INCREMENT BY 1
NOMAXVALUE;

CREATE OR REPLACE TRIGGER movTrigger
BEFORE INSERT
ON Movies
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT movSeq.nextval INTO :NEW.MovID FROM dual;
END;
/

CREATE TABLE UserContent
(
UserID INT NOT NULL,
MovID INT NOT NULL,
ContentID INT NOT NULL,
ContentType INT NOT NULL,
CreateDate DATE NOT NULL,
Rating FLOAT,
Text varchar2(4000),
FOREIGN KEY (UserID) REFERENCES Users (UserID) ON DELETE CASCADE,
FOREIGN KEY (MovID) REFERENCES Movies (MovID) ON DELETE CASCADE,
PRIMARY KEY (UserID, MovID, ContentID)
);

CREATE SEQUENCE usercontentSeq
START WITH 1
INCREMENT BY 1
NOMAXVALUE;

CREATE OR REPLACE TRIGGER userContentTrigger
BEFORE INSERT
ON UserContent
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT usercontentSeq.nextval INTO :NEW.ContentId FROM dual;
END;
/

CREATE SEQUENCE Images_seq
START WITH 1
INCREMENT BY 1
NOMAXVALUE;

CREATE TABLE Images (
ImgID int,
MovID int,
Image varchar2(100) NOT NULL,
Description varchar2(200) NOT NULL,
CreateDate date NOT NULL,
FOREIGN KEY (MovID) REFERENCES Movies (MovID) ON DELETE CASCADE,
PRIMARY KEY (ImgID)
);

CREATE OR REPLACE TRIGGER Images_trigger
BEFORE INSERT ON Images
FOR EACH ROW
BEGIN
SELECT Images_seq.nextval into :new.ImgId from dual;
END;
/

CREATE SEQUENCE Credits_seq
START WITH 1
INCREMENT BY 1
NOMAXVALUE;

CREATE TABLE Credits (
CreditID int,
JobTitle varchar2(50) NOT NULL,
FName varchar2(50) NOT NULL,
LName varchar2(50) NOT NULL,
ImgPortrait varchar2(100),
PRIMARY KEY (CreditID)
);

CREATE OR REPLACE TRIGGER Credits_trigger
BEFORE INSERT ON Credits
FOR EACH ROW
BEGIN
SELECT Credits_seq.nextval into :new.CreditId from dual;
END;
/

CREATE TABLE Tags (
CreditID int,
ImgID int,
PRIMARY KEY (CreditID, ImgID),
FOREIGN KEY (CreditID) REFERENCES Credits(CreditID) ON DELETE CASCADE,
FOREIGN KEY (ImgID) REFERENCES Images(ImgID) ON DELETE CASCADE
);

CREATE TABLE CreatedBy (
MovID int,
CreditID int,
PRIMARY KEY (MovID, CreditID),
FOREIGN KEY (MovID) REFERENCES Movies(MovID) ON DELETE CASCADE,
FOREIGN KEY (CreditID) REFERENCES Credits(CreditID) ON DELETE CASCADE
);

-- show errors trigger tr_a_UserInsert

insert into movies (MovTitle, SubmitterID, MovDesc, MovYear, MovBudget, PendStatus, ImgCover) values ('Mission Impossible', 2, 'An American agent, under false suspicion of disloyalty, must discover and expose the real spy without the help of his organization.', 1996, 80000000, 0, 'Mission%20Impossible.jpg');
